SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




CREATE PROCEDURE [dbo].[FSEPSOI_VC] AS
--***re-insert these lines

BEGIN

 
 
 DECLARE @FundID VARCHAR(10)
 SET @FundID = 'FSEPF'
  SET FMTONLY ON;
        DECLARE @fmtonlyON BIT
        SET @fmtonlyON = 0
        IF ( 1 = 0 ) 
            BEGIN
                SET @fmtonlyON = 1
            END
        SET FMTONLY OFF;
           
           
        SET NOCOUNT ON;
 
 DECLARE @ExportId INT =
            (SELECT MAX(ExportId)
            FROM    FSFundModel_VALCOMM.Reporting.Export
            WHERE   fundid = @FundID )
 
 


 
 DECLARE @Data TABLE(
      instrumentID INT ,
	  PortfolioID VARCHAR( MAX	),
      IssuerName VARCHAR(MAX) ,
      FootNotes VARCHAR(MAX) ,
      Industry VARCHAR(MAX) ,
      EBITDA DECIMAL(38, 10) ,
	  PropReason varchar(max) ,
	  PctProp DECIMAL(38, 10) ,
	  PricingSource varchar(max) , 
	  InvestmentRating int,
	  MoodysRating varchar(max) ,
	  BidPr DECIMAL(38, 10) ,
	  AskPr DECIMAL(38, 10) ,
	  TodaysPrice DECIMAL(38, 10) ,
	  Rate VARCHAR(MAX) ,
      Maturity Date ,
      PrincipalAmt DECIMAL(38, 10) ,
      AmortizedCost DECIMAL(38, 10) ,
      FairVal DECIMAL(38, 10) ,
      PIK DECIMAL(38, 10) ,
      RateType VARCHAR(MAX) ,
      BaseRateType VARCHAR(MAX) ,
      FixedRate DECIMAL(38, 10) ,
      Spread DECIMAL(38, 10) ,
      FLOOR DECIMAL(38, 10) ,
	  InstrClass Varchar(MAX), 
	  SortOrder INT,
	  QualAsset BIT,
	  CostDiscCurrPrc DECIMAL(38,10),
	  instTypeDescr VARCHAR( MAX	),
	  FinType VARCHAR(MAX ), 
	  IncomeAtCurrentPrice DECIMAL(38, 10),
	  OriginalCost DECIMAL(38, 10),
	  shares DECIMAL(38, 10),
	  OriginalPurchaseDate Date,
	  AtCost VARCHAR(MAX)
    )
--FORMAT_ON
--FORMAT_OFF
 DECLARE @ConsolidatedData TABLE(
      instrumentID INT ,
	  PortfolioID VARCHAR( MAX	),
      IssuerName VARCHAR(MAX) ,
      FootNotes VARCHAR(MAX) ,
      Industry VARCHAR(MAX) ,
      EBITDA DECIMAL(38, 10) ,
	  PropReason varchar(max) ,
	  PctProp DECIMAL(38, 10) ,
	  PricingSource varchar(max) ,
	  InvestmentRating int,
	  MoodysRating varchar(max) ,
	  BidPr DECIMAL(38, 10) ,
	  AskPr DECIMAL(38, 10) ,
	  TodaysPrice DECIMAL(38, 10) ,
	  Rate VARCHAR(MAX) ,
      Maturity Date ,
      PrincipalAmt DECIMAL(38, 10) ,
      AmortizedCost DECIMAL(38, 10) ,
      FairVal DECIMAL(38, 10) ,
      PIK DECIMAL(38, 10) ,
      RateType VARCHAR(MAX) ,
      BaseRateType VARCHAR(MAX) ,
      FixedRate DECIMAL(38, 10) ,
      Spread DECIMAL(38, 10) ,
      FLOOR DECIMAL(38, 10) ,
	  InstrClass Varchar(MAX), 
	  SortOrder INT,
	  QualAsset BIT,
	  CostDiscCurrPrc DECIMAL(38,10),
	  instTypeDescr VARCHAR( MAX	),
	  FinType VARCHAR(MAX ),
	  IncomeAtCurrentPrice DECIMAL(38, 10),
	  OriginalCost DECIMAL(38, 10),
	  shares DECIMAL(38, 10),
	  OriginalPurchaseDate Date ,
	  atcost VARCHAR(MAX)
    )
--FORMAT_ON
--FORMAT_OFF
DECLARE @Results TABLE (
	  InstrumentID INT,
      IssuerName VARCHAR(MAX) ,
      FootNotes VARCHAR(MAX) ,
      Industry VARCHAR(MAX) ,
      EBITDA varchar(MAX) ,
	  PropReason varchar(max) ,
	  PctProp DECIMAL(38, 10) ,
	  PricingSource varchar(max) ,
	  InvestmentRating int,
	  MoodysRating varchar(max) ,
	  BidPr DECIMAL(38, 10) ,
	  AskPr DECIMAL(38, 10) ,
	  TodaysPrice DECIMAL(38, 10) ,
	  Rate VARCHAR(MAX) ,
	  FLOOR VARCHAR(MAX	),
	  Maturity VARCHAR(MAX) ,
      PrincipalAmt DECIMAL(38, 10) ,
      AmortizedCost DECIMAL(38, 10) ,
      FairVal DECIMAL(38, 10) , 
	  sortOrder INT,
	  InstrType VARCHAR(MAX),
	  IncomeAtCurrentPrice DECIMAL(38, 10),
	  OriginalCost DECIMAL(38, 10),
	  shares DECIMAL(38, 10),
	  OriginalPurchaseDate Date,
	  atcost VARCHAR(MAX)
	  )
 INSERT INTO @Data
             (
                         instrumentID
						, PortfolioID
                       , IssuerName
                       , FootNotes
                       , Industry
                       , EBITDA
					   , PropReason
					   , PctProp
					   , PricingSource
					   , InvestmentRating
					   , MoodysRating
					   , BidPr
					   , AskPr
					   , TodaysPrice
					   , Rate
                       , Maturity
                       , PrincipalAmt
                       , AmortizedCost
                       , FairVal
                       , PIK
                       , RateType
                       , BaseRateType
                       , FixedRate
                       , Spread
                       , FLOOR
                       , InstrClass
						, SortOrder        
						, QualAsset        
						, CostDiscCurrPrc
						, instTypeDescr
						, FinType
						, IncomeAtCurrentPrice 
						, OriginalCost
						, shares
						, OriginalPurchaseDate
						, AtCost
				
             )
 
SELECT   i.InstrumentId
		, a.PortfolioId
        , iss.IssuerName
        , NULL
        , Iss.GSOInd
        , Iss.TTMEBITDA
		, ah.NBSReason
		, case when ah.NBSReason <> '' then a.mktval else '0' end
		, i.PricingVendor
		, i.AssQualRat
		, i.MoodysRating
		, pr.Bid
		, pr.Offer
		, a.TodayPrice
		, NULL
		, i.MaturityDate 
		, a.EndAdjAmt
		, a.AmortCost
		, a.MktVal
		, i.PIK
		, i.RateType
		, i.BaseRateType
		, i.FixedRate
		, i.Spread
		, i.Floor 
		, id.InstrClass    
		, id.SortOrder
		, fi.IsQualified
		, a.PctCostDiscCurrPrc
		, id.InstTypeDescr
		, id.FinType
		, a.TotalIntCurrPurchPrc
		, a.Cost
		, a.Shares
		, a.PurchDate
		, i.PricingMethodType
 

 
 FROM       FSFundModel_VALCOMM.Reporting.Asset a
            INNER JOIN FSFundModel_VALCOMM.Reporting.Instrument i
            ON         i.ExportId     = a.ExportId
            AND        a.InstrumentId = i.InstrumentId
            INNER JOIN FSFundModel_VALCOMM.Reporting.Issuer iss
            ON         iss.ExportId = a.ExportId
            AND        i.IssuerId   = iss.IssuerId
            INNER JOIN FSFundModel_VALCOMM.Reporting.InstrumentDefinition id
            ON         i.InstDefId = id.InstDefId
            AND        id.ExportId = a.ExportId
            INNER JOIN FSFundModel_VALCOMM.Reporting.FundIssuer fi
            ON         a.ExportId  = fi.ExportId
            AND        fi.IssuerId = iss.IssuerId
            INNER JOIN FSFundModel_VALCOMM.Reporting.Portfolio p
            ON         a.ExportId    = p.ExportId
            AND        a.PortfolioId = p.PortfolioId
            INNER JOIN FSFundModel_VALCOMM.Reporting.Fund f
            ON         a.ExportId = f.ExportId
			
			


			LEFT JOIN FSFundModel_VALCOMM.Reporting.Pricing Pr
			ON			a.ExportId = pr.ExportId
			AND			cast(f.CurrNavDate as date) = pr.PriceDate
			AND			i.ExtId = pr.PricingId

			
			INNER JOIN reportingscripts.valcomm.AssetHelper (@exportid) AH on ah.AssetIntrnlId = a.AssetIntrnlId



 WHERE      a.ExportId = @ExportId

		AND a.IsActiveEndOfDay =1



 UPDATE @Data 
 SET FootNotes = COALESCE(FootNotes + '(Term Funding)', '(Term Funding)') WHERE PortfolioID = 'FSEPT'
 UPDATE @Data  
 SET FootNotes = COALESCE(FootNotes + '(Energy Funding LLC)', '(Energy Funding LLC)') WHERE LTRIM(RTRIM(PortfolioID ))= 'FSEFL'
 Update @Data
 SET FootNotes = COALESCE(FootNotes + '(EP Funding)', '(EP Funding)') WHERE LTRIM(RTRIM(PortfolioID ))= 'FSEPF'
 Update @Data
 SET FootNotes = COALESCE(FootNotes + '(Berwyn Funding)', '(Berwyn Funding)') WHERE LTRIM(RTRIM(PortfolioID ))= 'FSEPB'


  INSERT INTO @ConsolidatedData
             (
                         instrumentID
					   , PortfolioID
                       , IssuerName
                       , FootNotes
                       , Industry
                       , EBITDA
					   , PropReason
					   , PctProp 
					   , PricingSource
					   , InvestmentRating
					   , MoodysRating
					   , BidPr
					   , AskPr
					   , TodaysPrice
					   , Rate
                       , Maturity
                       , PrincipalAmt
                       , AmortizedCost
                       , FairVal
                       , PIK
                       , RateType
                       , BaseRateType
                       , FixedRate
                       , Spread
                       , FLOOR
                       , InstrClass
					   , SortOrder        
						, QualAsset         
						, CostDiscCurrPrc
						, instTypeDescr
						, FinType 
						, IncomeAtCurrentPrice
						, OriginalCost
						, shares
						, OriginalPurchaseDate
						, atcost 
             )


 SELECT     d.InstrumentId
		, MAX(d.PortfolioId)
        , MAX(d.IssuerName)
        ,  NULL
        , MAX(d.industry)
        , MAX(d.EBITDA)
		, MAX(d.propreason)
		, SUM(d.pctprop) 
		, MAX(d.PricingSource)
		, max(d.investmentrating)	
        , max(d.moodysrating)
		, max(d.bidpr)
		, max(d.askpr)
		, MAX(d.todaysprice)
		,  NULL
		, MAX(d.Maturity)
		, SUM(d.PrincipalAmt)
		, SUM(d.AmortizedCost)
		, SUM(d.FairVal)
		, MAX(d.PIK)
		, max(d.RateType)
		, max(d.BaseRateType)
		, max(d.FixedRate)
		, max(d.Spread)
		, max(d.Floor )
		, max(d.InstrClass)
		, max(d.SortOrder)
		, d.QualAsset
		, MAX(d.CostDiscCurrPrc)
		, MAX(d.instTypeDescr)
		, MAX(d.FinType)
		, sum(d.IncomeAtCurrentPrice)
		, sum(d.originalcost)
		, SUM(d.shares)
		, min(d.OriginalPurchaseDate)
		, MAX(d.atcost)

 

FROM @Data d


GROUP BY d.instrumentId , d.QualAsset

 
 
 INSERT INTO @Results
         ( InstrumentID ,
           IssuerName ,
           Industry ,   
		   EBITDA ,
		   PropReason ,
		   PctProp ,
		   PricingSource ,
		   InvestmentRating ,
		   MoodysRating ,
		   BidPr ,
		   AskPr , 
		   TodaysPrice ,
		   FootNotes ,                
           PrincipalAmt ,
           AmortizedCost ,
           FairVal ,
		   sortOrder ,
		   InstrType ,
		   IncomeAtCurrentPrice,
		   OriginalCost ,
		   shares ,
		   OriginalPurchaseDate,
		   atcost
         )

 SELECT 
	DISTINCT d.InstrumentID,
	d.IssuerName, 
	d.Industry,  
	d.EBITDA,
	d.PropReason ,
	case when d.fairval = '0' then '0' else d.pctprop / d.FairVal end,
	d.PricingSource,
	d.InvestmentRating ,
	d.MoodysRating ,
	d.Bidpr,
	d.askpr,
	d.TodaysPrice ,
	STUFF((
			SELECT '' + tmp.FootNotes FROM (SELECT 
			 DISTINCT d.instrumentID,d.IssuerName, d.FootNotes
			from 
				@Data d ) AS tmp
			WHERE tmp.instrumentid = d.instrumentID
				FOR XML PATH ('')
				), 1,0, ''),
				 d.PrincipalAmt ,
      d.AmortizedCost  ,
      d.FairVal  ,
	  d.sortorder,
	  d.instrClass ,
	  d.IncomeAtCurrentPrice,
	  d.OriginalCost ,
	  d.shares ,
	  d.OriginalPurchaseDate,
	  d.atcost

 FROM	@ConsolidatedData d
 
 ORDER BY d.sortOrder ASC, d.issuerName ASC , d.instrumentid 

  Update  r
 SET r.FootNotes = COALESCE(r.FootNotes + '(Non-Qualified)', '(Non-Qualified)') 
 
 FROM @Results r
 INNER JOIN @ConsolidatedData d ON r.IssuerName= d.issuerName 
  WHERE d.QualAsset = 0

  UPDATE r
  SET  r.Rate = CASE WHEN d.RateType LIKE '%Floating%' THEN
				CASE WHEN d.BaseRateType LIKE '%LIBOR%' THEN 'L+' + CAST (CAST	((d.spread*10000) AS DECIMAL(38,0)) AS varchar (MAX))
				     when d.BaseRateType LIKE '%EURIBOR%' THEN 'EURIBOR+' +  CAST (CAST	((d.spread*10000) AS DECIMAL(38,0)) AS varchar (MAX))
					 when d.BaseRateType LIKE '%PRIME%' THEN 'Prime+' +  CAST (CAST	((d.spread*10000) AS DECIMAL(38,0)) AS varchar (MAX))
				END 
			END 
FROM @Results r 
INNER JOIN @ConsolidatedData d ON r.InstrumentID = d.instrumentId

UPDATE r
SET r.Rate  = CASE WHEN d.rateType = 'Fixed' THEN CAST (CAST	((d.fixedRate*100) AS DECIMAL(38,1)) AS varchar (MAX)) + '%'
				ELSE r.Rate
			END	
FROM @Results r 
INNER JOIN @ConsolidatedData d ON r.InstrumentID = d.instrumentId



UPDATE r --%O - % matches for Collateralized Secs 
SET r.Rate  = CASE WHEN d.rateType = 'None' THEN 
				case when d.instTypeDescr LIKE '%O - %' then CAST (CAST	((d.CostDiscCurrPrc *100) AS DECIMAL(38,1)) AS varchar (MAX)) + '%'
				END 
				ELSE r.Rate
			END	
FROM @Results r 
INNER JOIN  @ConsolidatedData d ON r.InstrumentID = d.instrumentID

UPDATE r
SET r.FLOOR = CASE WHEN d.RateType LIKE '%Floor%' THEN 
				CASE WHEN d.BaseRateType LIKE '%LIBOR%' THEN CAST ( CAST ((d.FLOOR * 100 ) AS DECIMAL (38,1) ) AS varchar(MAX)) + '% LIBOR Floor'
				WHEN d.baseRateType LIKE '%EURIBOR%' then CAST ( CAST ((d.FLOOR * 100 ) AS DECIMAL (38,1) ) AS varchar(MAX)) + '% EURIBOR Floor'
			    WHEN d.baseRateType LIKE '%PRIME%' then CAST ( CAST ((d.FLOOR * 100 ) AS DECIMAL (38,1) ) AS varchar(MAX)) + '% Prime Floor'
				END 
			End
FROM @Results r 
INNER JOIN @ConsolidatedData d ON r.instrumentid = d.instrumentid


UPDATE r 
SET r.maturity = cast(( cast (datepart(month, d.Maturity) AS varchar(MAX)) + '/' + CAST( DATEPART(DAY, d.Maturity) AS varchar(MAX)) + 	
			'/' + right(CAST( DATEPART(year, d.Maturity) AS varchar(MAX)) , 2) ) AS varchar (MAX))
FROM @Results r
INNER JOIN @ConsolidatedData d ON R.InstrumentID = d.instrumentID

 SELECT r.* FROM @Results r 
		WHERE r.instrumentid NOT IN (
		SELECT fci.instrumentID FROM FSFundModel_VALCOMM.Reporting.FundCreditInstrument fci
		WHERE 
		fci.ExportId = (SELECT MAX(e.ExportId) FROM fsfundmodel_VALCOMM.Reporting.Export e WHERE e.FundId ='FSEPF') and fci.Drawn = '0')
END






GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Dbo/QuarterlyReporting/SEC', 'SCHEMA', N'dbo', 'PROCEDURE', N'FSEPSOI_VC', NULL, NULL
GO
